<?php

namespace App\Store\Controllers\Financial\Goods;

use App\Common\Services\CommonExcelExport;
use App\Common\Services\Store\Common as StoreCommon;
use App\Http\Controllers\Controller;
use App\Store\Models\Account\SiteStore;
use App\Store\Models\Goods\GoodsSend\GoodsSendgoods;
use App\Store\Models\Goods\GoodsSend\GoodsSendgoodsAllot;
use App\Store\Models\Goods\GoodsSend\GoodsSendgoodsStock;
use App\Store\Models\Order\OrderSalesSendGoods;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Maatwebsite\Excel\Facades\Excel;


class GoodsSendGoodsController extends Controller
{
    public function getlist(Request $request)
    {
        $data = $this->getdata($request);
        return response()->json(['code' => '000', 'msg' => '获取成功', 'data' => $data]);
    }

    public function getdatabyymd($ymd, $store_ids)
    {
        // 获取数据库配置前缀
        $databasePrefix = env('DB_PREFIX', '');

        $table_name = (new GoodsSendgoodsStock())->getTable() . '_' . $ymd;

        if (!Schema::hasTable($table_name)) {
            return [];
        }

        $query = Db::table($table_name)
            ->join('site_store_list', $table_name . '.store_id', '=', 'site_store_list.id')
            ->where([
                $table_name.'.is_del' => 1
        ]);
        if (!empty($store_ids)) {
            $query->whereIn($table_name . '.store_id', $store_ids);
        }

        $list = $query->select($table_name.'.*', 'site_store_list.store_name', DB::raw("sum($databasePrefix$table_name.count) as count, sum($databasePrefix$table_name.pieceprice * $databasePrefix$table_name.count) as pieceprice"))
            ->groupBy('goods_sn_code')
            ->get()
            ->toArray();
        $data = [];
        foreach ($list as $k => $v) {
            $data[$k] = json_decode(json_encode($v, true), true);
        }
        return $data;
    }

    public function getdata(Request $request)
    {
        $start_time = (!is_numeric($request->start_time) && strtotime($request->start_time) > 0) ? strtotime($request->start_time) : intval($request->start_time);
        $end_time = (!is_numeric($request->end_time) && strtotime($request->end_time) > 0) ? strtotime($request->end_time) : intval($request->end_time);
        if ($start_time == 0 || $end_time == 0) {
            return [];
        }


        $store_id = $request['store_ids'] ?? '';
        $store_ids = [];
        if (!empty($store_id)) {
            $store_ids = is_array($store_id) ? $store_id : (is_array(json_decode($store_id, true)) ? json_decode($store_id, true) : []);
        }


        $sendList = (new GoodsSendgoodsStock())->with('storeinfo')
            ->where(['is_del' => 1,'store_id'=>$store_ids])
            ->when(@empty($store_ids),function ($query) use ($store_ids){
                $query->whereIn( '.store_id', $store_ids);
            })
            ->get()
            ->toArray();



        //期初库存 取开始时间的前一天的数据
        $start = date('Ymd', $start_time);
        $start = date('Ymd', strtotime($start . " -1 day"));

        $res = $this->getdatabyymd($start, $store_ids);


        $start_arr =[];
        if ($res) {
            foreach ($res as $v) {
                $start_arr[$v['goods_sn_code']]['store_name'] = $v['store_name'] ?? '';
                $start_arr[$v['goods_sn_code']]['sendgoods_name'] = $v['sendgoods_name'] ?? '';
                $start_arr[$v['goods_sn_code']]['start'] = $v;
            }
        }

        //期末库存
        $end = date('Ymd', $end_time);
        $res = $this->getdatabyymd($end, $store_ids);

        $end_arr=[];
        if ($res) {
            foreach ($res as $v) {
                $end_arr[$v['goods_sn_code']]['store_name'] = $v['store_name'] ?? '';
                $end_arr[$v['goods_sn_code']]['sendgoods_name'] = $v['sendgoods_name'] ?? '';
                $end_arr[$v['goods_sn_code']]['end'] = $v;
            }
        }

        //入库成本
        $query = GoodsSendgoods::query()->with('storeinfo')
            ->select('*', DB::raw('sum(count) as count, sum(pieceprice*count) as pieceprice'))
            ->where(['is_del' => 1,'audit_status'=>2])
            ->where('audit_at','>=',$start_time)
            ->where('audit_at','<=',$end_time);
        if (!empty($store_ids)) {
            $query->whereIn('store_id', $store_ids);
        }
        $res = $query->groupBy('goods_sn_code')->get()->toArray();

        $stock_ins =[];
        if ($res) {
            foreach ($res as $v) {
                $stock_ins[$v['goods_sn_code']]['store_name'] = $v['storeinfo']['store_name'] ?? '';
                $stock_ins[$v['goods_sn_code']]['sendgoods_name'] = $v['sendgoods_name'] ?? '';
                $stock_ins[$v['goods_sn_code']]['stock_in'] = $v;
            }
        }

        //调拨入库
        $query = GoodsSendgoodsAllot::with('targetstoreinfo')->select('*', DB::raw('sum(count) as count, sum(pieceprice*count) as pieceprice'))
            ->where(['is_del' => 1, 'audit_status' => 2])
            ->where('audit_at','>=',$start_time)
            ->where('audit_at','<=',$end_time);;
        if (!empty($store_ids)) {
            $query->whereIn('target_store_id', $store_ids);
        }
        $res = $query->groupBy('goods_sn_code')->get()->toArray();

        $allot_ins=[];
        if ($res) {
            foreach ($res as $v) {
                $v['storeinfo']=$v['targetstoreinfo']?? [];
                unset($v['targetstoreinfo']);
                $allot_ins[$v['goods_sn_code']]['store_name'] = $v['storeinfo']['store_name'] ?? '';
                $allot_ins[$v['goods_sn_code']]['sendgoods_name'] = $v['sendgoods_name'] ?? '';
                $allot_ins[$v['goods_sn_code']]['allot_in'] = $v;
            }
        }

        //调拨出库
        $query = GoodsSendgoodsAllot::with('storeinfo')->select('*', DB::raw('sum(count) as count, sum(pieceprice*count) as pieceprice'))
            ->where(['is_del' => 1, 'audit_status' => 2])
            ->where('audit_at','>=',$start_time)
            ->where('audit_at','<=',$end_time);
        if (!empty($store_ids)) {
            $query->whereIn('store_id', $store_ids);
        }
        $res = $query->groupBy('goods_sn_code')->get()->toArray();
        $allot_outs=[];
        if ($res) {
            foreach ($res as $v) {
                $allot_outs[$v['goods_sn_code']]['store_name'] = $v['storeinfo']['store_name'] ?? '';
                $allot_outs[$v['goods_sn_code']]['sendgoods_name'] = $v['sendgoods_name'] ?? '';
                $allot_outs[$v['goods_sn_code']]['allot_out'] = $v;
            }
        }

        //销售出库
        $query = OrderSalesSendGoods::with('storeinfo')
            ->select('*', DB::raw('sum(count) as count, sum(pieceprice*count) as pieceprice'))
            ->where(['is_del' => 1])
            ->where('order_at','>=',$start_time)
            ->where('order_at','<=',$end_time);
        if (!empty($store_ids)) {
            $query->whereIn('store_id', $store_ids);
        }
        $res = $query->groupBy('goods_sn_code')->get()->toArray();



        $sales_arr =[];
        foreach ($res as $v) {
            $goods_sn_code = json_decode($v['send_goods_info'],true)['goods_sn_code']?? '';
            if(!empty($goods_sn_code)){
                $sales_arr[$goods_sn_code]['store_name'] = $v['storeinfo']['store_name'] ?? '';
                $sales_arr[$goods_sn_code]['sendgoods_name'] = $v['sendgoods_name'] ?? '';
                $sales_arr[$goods_sn_code]['sales'] = $v;
            }

        }


        $data=[];
        foreach ($sendList as $item) {
            $start = $start_arr[$item['goods_sn_code']]['start'] ?? [];
            $end = $end_arr[$item['goods_sn_code']]['end'] ?? [];
            $stock_in = $stock_ins[$item['goods_sn_code']]['stock_in'] ?? [];
            $allot_in = $allot_ins[$item['goods_sn_code']]['allot_in'] ?? [];
            $allot_out = $allot_outs[$item['goods_sn_code']]['allot_out'] ?? [];
            $sales=$sales_arr[$item['goods_sn_code']]['sales'] ?? [];

            if (!empty($start) || !empty($end) || !empty($stock_in) || !empty($allot_in) || !empty($allot_out) || !empty($sales)) {

                $data[$item['goods_sn_code']]['store_name'] = $item['storeinfo']['store_name'];
                $data[$item['goods_sn_code']]['sendgoods_name'] = $item['sendgoods_name'];
                $data[$item['goods_sn_code']]['goods_sn_code'] = $item['goods_sn_code'];
                $data[$item['goods_sn_code']]['goods_sn_code'] = $item['goods_sn_code'];
                $data[$item['goods_sn_code']]['count'] = $item['count'];
                $data[$item['goods_sn_code']]['pieceprice'] = $item['pieceprice'];
                $data[$item['goods_sn_code']]['start'] = $start;
                $data[$item['goods_sn_code']]['end'] = $end;
                $data[$item['goods_sn_code']]['stock_in'] = $stock_in;
                $data[$item['goods_sn_code']]['allot_in'] = $allot_in;
                $data[$item['goods_sn_code']]['allot_out'] = $allot_out;
                $data[$item['goods_sn_code']]['sales'] = $sales;
            }
        }
        return array_values($data);
    }

    public function doexport(Request $request)
    {
        $data = $this->getdata($request);
        if (!$data) {
            return response()->json(['code' => '001', 'msg' => '导出失败']);
        }

        $store_id = $request['store_ids'];
        $store_id = is_array($store_id) ? $store_id : (is_array(json_decode($store_id, true)) ? json_decode($store_id, true) : []);
        $store_names=SiteStore::whereIn('id',$store_id)->pluck('store_name')->toArray();


        $start_time = $request->start_time;
        $end_time = $request->end_time;

        $row = [
            ['store_name'=>'赠品汇总表'],
            ['store_name'=>'所属门店：'.join(',',$store_names).$start_time.' - '.date('Y-m-d',strtotime($end_time))],
            [
            'store_name' => '所属门店',
            'sendgoods_name' => '赠品名称',
            'start_count' => '期初-库存',
            'start_pieceprice' => '期初-成本',
            'stock_in_count' => '入库-件数',
            'stock_in_pieceprice' => '入库-成本',
            'allot_in_count' => '调拨入库-件数',
            'allot_in_pieceprice' => '调拨入库-成本',
            'allot_out_count' => '调拨出库-件数',
            'allot_out_pieceprice' => '调拨出库-成本',
            'sales_count' => '销售出库-件数',
            'sales_pieceprice' => '销售出库-成本',
            'end_count' => '期末-件数',
            'end_pieceprice' => '期末-成本',
        ]];

        $exceldata = [];

        $is_store_account = StoreCommon::getIsStoreAccount($request);
        if (!$is_store_account) {
            unset($row[2]['start_pieceprice']);
            unset($row[2]['allot_in_pieceprice']);
            unset($row[2]['allot_out_pieceprice']);
            unset($row[2]['sales_pieceprice']);
            unset($row[2]['end_pieceprice']);
            foreach ($data as $val) {
                $exceldata[] = [
                    'store_name' => isset($val['store_name']) ? $val['store_name'] : '',
                    'sendgoods_name' => isset($val['sendgoods_name']) ? $val['sendgoods_name'] : '',
                    'start_count' => isset($val['start']['count']) ? $val['start']['count'] : '0',
                    'stock_in_count' => isset($val['stock_in']['count']) ? $val['stock_in']['count'] : '0',
                    'stock_in_pieceprice' => isset($val['stock_in']['pieceprice']) ? $val['stock_in']['pieceprice'] / 100 : '0',
                    'allot_in_count' => isset($val['allot_in']['count']) ? $val['allot']['count'] : '0',
                    'allot_out_count' => isset($val['allot_out']['count']) ? $val['allot']['count'] : '0',
                    'sales_count' => isset($val['sales']['count']) ? $val['sales']['count'] : '0',
                    'end_count' => isset($val['end']['count']) ? $val['end']['count'] : '0',
                ];
            }
            $mergecell = ['A1:H1','A2:H2'];
        }else{
            foreach ($data as $val) {
                $exceldata[] = [
                    'store_name' => isset($val['store_name']) ? $val['store_name'] : '',
                    'sendgoods_name' => isset($val['sendgoods_name']) ? $val['sendgoods_name'] : '',
                    'start_count' => isset($val['start']['count']) ? $val['start']['count'] : '0',
                    'start_pieceprice' => isset($val['start']['pieceprice']) ? $val['start']['pieceprice'] / 100 : '0',
                    'stock_in_count' => isset($val['stock_in']['count']) ? $val['stock_in']['count'] : '0',
                    'stock_in_pieceprice' => isset($val['stock_in']['pieceprice']) ? $val['stock_in']['pieceprice'] / 100 : '0',
                    'allot_in_count' => isset($val['allot_in']['count']) ? $val['allot_in']['count'] : '0',
                    'allot_in_pieceprice' => isset($val['allot_in']['pieceprice']) ? $val['allot_in']['pieceprice'] / 100 : '0',
                    'allot_out_count' => isset($val['allot_out']['count']) ? $val['allot_out']['count']: '0',
                    'allot_out_pieceprice' => isset($val['allot_out']['pieceprice']) ? $val['allot_out']['pieceprice'] / 100: '0',
                    'sales_count' => isset($val['sales']['count']) ? $val['sales']['count'] : '0',
                    'sales_pieceprice' => isset($val['sales']['pieceprice']) ? $val['sales']['pieceprice'] / 100 : '0',
                    'end_count' => isset($val['end']['count']) ? $val['end']['count'] : '0',
                    'end_pieceprice' => isset($val['end']['pieceprice']) ? $val['end']['pieceprice'] / 100 : '0',
                ];
            }
            $mergecell = ['A1:M1','A2:M2'];
        }

        $filename = '赠品报表导出_' . time() . '.xlsx';
        return Excel::download(new CommonExcelExport($row, $exceldata,[],$mergecell), $filename);
    }
}
